package org.example.user.statistic.repository;

import org.example.commons.entity.SysAdmin;
import org.example.commons.entity.SysHealthRecord;
import org.example.commons.entity.SysHealthStatus;
import org.example.commons.entity.SysUser;
import org.example.user.statistic.dto.SysHealthRecordDTO;
import org.example.user.statistic.form.HealthRecordSearchForm;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;

/**
 * 扫描记录 数据层
 * @author bothin
 */
@Repository
public interface SysHealthRecordRepository extends JpaRepository<SysHealthRecord,Long> {
    List<SysHealthRecord> findByRecordIdIn(Long[] ids);

    Page<SysHealthRecord> findByAdminId(Long adminId, Pageable pageable);

    Page<SysHealthRecord> findByUserId(Long userId, Pageable pageable);

    @Query("select new org.example.user.statistic.dto.SysHealthRecordDTO(record.recordId,record.userId,u.name," +
            "record.adminId,admin.name,record.passStatus,record.passResult,record.temperature," +
            "record.healthStatus,record.recordAddress,record.tripDesc,record.remarks,record.recordTime) " +
            "from SysHealthRecord record left join SysUser u on record.userId = u.userId " +
            "left join SysAdmin admin on record.adminId = admin.adminId " +
            "where u.name like :#{#form.userName} and admin.name like :#{#form.adminName} " +
            " and record.recordAddress like :#{#form.recordAddress} " +
            " and (:#{#form.passResult} IS NULL or record.passResult = :#{#form.passResult} ) " +
            " and (:#{#form.passStatus} IS NULL or record.passStatus = :#{#form.passStatus} ) " +
            " and (:#{#form.healthStatus} IS NULL or record.healthStatus = :#{#form.healthStatus} ) " +
            "and ( :#{#form.dates[0]} IS NULL OR :#{#form.dates[1]} IS NULL OR " +
            "(record.recordTime between :#{#form.dates[0]} and :#{#form.dates[1]})) ")
    Page<SysHealthRecordDTO> searchList(HealthRecordSearchForm form, Pageable pageable);

    /*    days天内一共有多少*/
    @Query(value = "SELECT * FROM sys_health_record  where DATE_SUB(CURDATE(), INTERVAL ? DAY) >= date(record_time)",nativeQuery =true)
    List<SysHealthRecord>  findLastSysHealthRecords(Integer days);
    /*  某用户最近times次体温记录*/
    @Query(value = "select  r.temperature  from sys_health_record r  where user_id =? order by record_time desc limit ?",nativeQuery =true)
    List<Float> findLastTimesUser(Long userId, Integer times);
    /*  某用户最近times次时间记录*/
    @Query(value = "select  r.record_time  from sys_health_record r  where user_id =? order by record_time desc limit ?",nativeQuery =true)
    List<Date> findLastTimesUserRecord(Long userId, Integer times);

    /*前day1~day2天记录*/
    @Query(value = "SELECT * FROM sys_health_record  where  DATE_SUB(CURDATE(), INTERVAL ? DAY) > date(record_time) and DATE_SUB(CURDATE(), INTERVAL  ? DAY) <= date(record_time) ",nativeQuery =true)
    List<SysHealthRecord>  findTimeSysHealthRecords(Integer day1, Integer day2);

    /*前day1~day2天记录体温平均值*/
    @Query(value = "SELECT AVG(temperature) FROM sys_health_record  where  DATE_SUB(CURDATE(), INTERVAL ? DAY) > date(record_time) and DATE_SUB(CURDATE(), INTERVAL  ? DAY) <= date(record_time) ",nativeQuery =true)
    Float  findTimeAvgTemperature(Integer day1, Integer day2);

    /*前day1~day2天输入记录数量*/
    @Query(value = "SELECT SUM(record_id) FROM sys_health_record  where  DATE_SUB(CURDATE(), INTERVAL ? DAY) > date(record_time) and DATE_SUB(CURDATE(), INTERVAL  ? DAY) <= date(record_time) ",nativeQuery =true)
    Long findTimeSum(Integer day1, Integer day2);
}
